const sql = require('mssql')

/**
 * 查询商品库存
 */
const reviewsCount = (bm, ph, ckbm, yxqz, scrq, cbjj) => {
    return sql.query`SELECT KCSL FROM KC_KCDTB WHERE BMBM = 07 AND YPBM = ${bm} AND SCPH = ${ph} AND CKBM = ${ckbm}  `
}

/**
 * 查询订单状态
 */
const reviewsOrder = id => {
    return sql.query`SELECT SH FROM XDD_ZB WHERE ID = ${id}`
}

/**
 * 查询订单是否已经写入xsd_zb里面
 */
const isWrite = id => {
    return sql.query`SELECT ID FROM XSD_ZB WHERE ID_DHD = ${id}`
}



/**
 * 查询订单
 */
const sendOrderData = body => {

    let searchSql = '';
    if (body.JBRBM) {
        searchSql += `AND JSFSBM = ${body.JBRBM}`
    }

    if (body.KHMC) {

        searchSql += `AND DWMC = '${body.KHMC}'`
    }

    let startSql = ` WITH t1 AS (SELECT ROW_NUMBER() OVER (ORDER BY RQ) PX,ID,DWBM,RQ,DWMC,JSFSBM,JSFSMC,ZSL,ZJS,ZSS,ZSE,ZHJ,ZJE,YJS,SH,BZ
    FROM XDD_ZB
    WHERE SH = 'T' AND NOT EXISTS (SELECT 1 FROM dbo.XSD_ZB WHERE XSD_ZB.ID_DHD=XDD_ZB.ID) AND RQ >= '${body.T1 + ' 00:00:00'}' AND 
    RQ <= '${body.T2 + ' 23:59:59'}' ${searchSql})
    SELECT t1.*,bm.DH,bm.DZ,bm.DWLX,
    (
        SELECT CASE WHEN SFMZ=0 THEN '满足' ELSE '不满足' END FROM 
        (
         SELECT SUM(CASE WHEN XSSL>ISNULL(s.KCSL,0) THEN 1 ELSE 0 END) SFMZ
         FROM 
         (
          SELECT SUM(SL) XSSL,(SELECT SUM(KCSL) FROM dbo.KC_KCDTB WHERE dbo.KC_KCDTB.YPBM=dbo.XDD_MX.YPBM AND BMBM='07') KCSL
          FROM dbo.XDD_MX WHERE dbo.XDD_MX.ID=t1.ID
          GROUP BY YPBM
         )s
        )s
          ) SFMZ 
    FROM t1
    LEFT JOIN BM_WLDW AS bm ON bm.BM=t1.DWBM
    WHERE t1.PX>${(body.NUM - 1) * 10} AND t1.PX<=${body.NUM * 10} ORDER BY t1.RQ `;


    return sql.query(startSql)

}

/**
 * 查询商品的详细信息
 */
const sendDetail = ({ id, BMBM, DWBM, DWLX }) => {


    // 没有经营范围筛选的
    // let startSql = `SELECT m.ID,m.YPBM,m.YPMC,m.YPGG,m.JLDW,m.SCCJ,m.PZWH,m.SL,m.DJ,m.JE,m.ZK,m.JXMC,m.YJ,产品.SCPH,产品.RKRQ,产品.DWBM,产品.SCRQ,产品.YXQZ,产品.KCSL,产品.CKBM,产品.CKMC,产品.CBJJ,b.MJSL,b.SSXK,产品.HWBH,b.CCTJ,b.YXFL,b.CDMC,b.TM FROM (SELECT YPBM,SUM(SL) AS SL,ID,YPMC,YPGG,JLDW,SCCJ,PZWH,JXMC,(SUM(JE)/SUM(SL)) AS DJ,SUM(JE) as JE,ZK,YJ FROM XDD_MX GROUP BY YPBM,ID,YPMC,YPGG,JLDW,SCCJ,PZWH,JXMC,ZK,YJ
    // ) AS m , (SELECT * FROM KC_KCDTB K WHERE K.KCSL > 0) AS 产品,(SELECT * FROM BM_YP B)AS b WHERE m.YPBM = 产品.YPBM AND 产品.BMBM = '07' AND M.ID = ${id} AND b.BM = m.YPBM  `;

    // // 有经营范围筛选的
    // // let startSql = `SELECT m.ID,m.YPBM,m.YPMC,m.YPGG,m.JLDW,m.SCCJ,m.PZWH,m.SL,m.DJ,m.JE,m.ZK,m.JXMC,m.YJ,产品.SCPH,产品.RKRQ,产品.DWBM,产品.SCRQ,产品.YXQZ,产品.KCSL,产品.CKBM,产品.CKMC,产品.CBJJ,b.MJSL,产品.HWBH,b.CCTJ,b.YXFL,b.CDMC,b.TM FROM (SELECT YPBM,SUM(SL) AS SL,ID,YPMC,YPGG,JLDW,SCCJ,PZWH,JXMC,(SUM(JE)/SUM(SL)) AS DJ,SUM(JE) as JE,ZK,YJ FROM XDD_MX GROUP BY YPBM,ID,YPMC,YPGG,JLDW,SCCJ,PZWH,JXMC,ZK,YJ
    // // ) AS m , (SELECT * FROM KC_KCDTB K WHERE K.KCSL > 0) AS 产品,(SELECT * FROM BM_YP B)AS b WHERE m.YPBM = 产品.YPBM AND 产品.BMBM = '07' AND M.ID = ${id} AND b.BM = m.YPBM AND (SELECT JYFW FROM BM_BM WHERE BM = ${BMBM}) LIKE '%'+b.JYFL+'%' `;

    // // if(DWLX == 1||DWLX == 2){
    // //     startSql+=` AND (SELECT JYFW FROM BM_WLDW WHERE BM = ${DWBM}) LIKE '%'+b.JYFL+'%' `;
    // // }
    // startSql += ` ORDER BY 产品.YXQZ ASC`;
    let startSql = ` WITH t AS (
        SELECT YPBM,ID,YPMC,YPGG,JLDW,SCCJ,PZWH,JXMC,ZK,YJ,SUM(SL) AS SL,SUM(JE) AS JE,(SUM(JE)/SUM(SL)) AS DJ
        FROM XDD_MX 
        WHERE ID='${id}'
        GROUP BY YPBM,ID,YPMC,YPGG,JLDW,SCCJ,PZWH,JXMC,ZK,YJ)
        
        SELECT t.*,KC.SCPH,KC.RKRQ,KC.DWBM,KC.SCRQ,KC.YXQZ,KC.KCSL,KC.CKBM,KC.CKMC,KC.CBJJ,KC.HWBH,
        YP.MJSL,YP.SSXK,YP.CCTJ,YP.YXFL,YP.CDMC,YP.TM 
        FROM t 
        LEFT JOIN KC_KCDTB AS KC ON t.YPBM=KC.YPBM
        LEFT JOIN BM_YP AS YP ON t.YPBM=YP.BM
        WHERE KC.BMBM = '07' AND KC.KCSL>0 
        ORDER BY KC.YXQZ `
    return sql.query(startSql)
}

/**
 * 获取最新一条数据的ID
 */
const sendStream = () => {
    return sql.query`SELECT TOP 1 ID FROM [dbo].[XSD_ZB] ORDER BY ID DESC`
}

/**
 * 更新订单备注
 */
const setRemark = (BZ, ID) => {
    return sql.query`UPDATE XDD_ZB SET BZ = ${BZ} WHERE ID=${ID}`
}

const getZB = (ID) => {
    return sql.query`SELECT * FROM XSD_ZB WHERE ID = ${ID}`
}


/**
 * 写入销售订单总表
 */
const updateZB = (newID, newTime, j, body, abs, zcb) => {
    return ` INSERT INTO XSD_ZB (ID, RQ, DWBM, DWMC, LXDH, BMBM, BMMC, JBRBM, JBRMC, JSFSBM, JSFSMC, ZDR, SHR, ZDZK, SKQX, SKQZ, PJLX, ZSL, ZJS, ZSS, ZJE, ZSE, ZHJ, YJS, 
        XJS, EJS, WJS, ZZK, ZCB, ZML, YHE, DXJF, BCJF, CZJE, SK, ZL, LX, FS, SH, ID_DHD)
                SELECT '${newID}','${newTime}','${j.DWBM}','${j.DWMC}',' ${j.DH}','${
        body.value.BMBM
        }', '${body.value.BMMC}','${body.value.BMBM + body.value.JBRBM}','${
        body.value.JBRMC
        }','${j.JSFSBM}','${j.JSFSMC}','${body.value.ZDR}','${
        body.value.SHR
        }',${100},${365},'${abs}',${'0'} ,${j.ZSL},${j.ZJS} ,${j.ZSL} ,${
        j.ZJE
        } , ${j.ZSE},${j.ZHJ} , ${j.YJS}, ${j.ZJE},${j.ZJE}, ${j.ZHJ -
        j.ZJE},${0},${zcb},${j.ZJE - zcb},${0},${0},${parseInt(
            j.ZJE / 10
        )},${0},${j.ZJE},${0},'${body.value.LXMC}',${1},'${body.value.SH}','${
        j.ID
        }' `
    // return sql.query`INSERT INTO XSD_ZB (ID, RQ, DWBM, DWMC, LXDH, BMBM, BMMC, JBRBM, JBRMC, JSFSBM, JSFSMC, ZDR, SHR, ZDZK, SKQX, SKQZ, PJLX, ZSL, ZJS, ZSS, ZJE, ZSE, ZHJ, YJS, XJS, EJS, WJS, ZZK, ZCB, ZML, YHE, DXJF, BCJF, CZJE, SK, ZL, LX, FS, SH, ID_DHD)
    //             SELECT ${newID},${newTime},${j.DWBM},${j.DWMC}, ${j.DH},${
    //     body.value.BMBM
    //     }, ${body.value.BMMC},${body.value.BMBM + body.value.JBRBM},${
    //     body.value.JBRMC
    //     },${j.JSFSBM},${j.JSFSMC},${body.value.ZDR},${
    //     body.value.SHR
    //     },${100},${360},${abs},${'0'} ,${j.ZSL},${j.ZJS} ,${j.ZSL} ,${
    //     j.ZJE
    //     } , ${j.ZSE},${j.ZHJ} , ${j.YJS}, ${j.ZJE},${j.ZJE}, ${j.ZHJ -
    //     j.ZJE},${0},${zcb},${j.ZJE - zcb},${0},${0},${parseInt(
    //         j.ZJE / 10
    //     )},${0},${j.ZJE},${0},${body.value.LXMC},${1},${body.value.SH},${
    //     j.ID
    //     }
    //              `
}

/**
 * 写入销售订单JS表
 */
const updateJS = (newID, j, body) => {
    return `INSERT INTO XSD_JS (ID,XH,BM,MC,SK,ZL,JSJE,LX) SELECT '${newID}', ${1},'${
        j.JSFSBM
        }','${j.JSFSMC}',${j.ZJE},${j.ZL},${j.ZJE},${body.value.LX}`
    // return sql.query`INSERT INTO XSD_JS (ID,XH,BM,MC,SK,ZL,JSJE,LX) SELECT ${newID}, ${1},${
    //     j.JSFSBM
    //     },${j.JSFSMC},${j.ZJE},${j.ZL},${j.ZJE},${body.value.LX}`
}

/**
 * 更新商品库存
 */
const updateKC = (CXSL, we) => {

    // let judgeList = ['YXQZ', 'SCRQ', 'CBJJ', 'RKRQ', 'DWBM'];
    let updateKcSql = `UPDATE KC_KCDTB SET KCSL = ${CXSL -
        we.SL},KYSL =${CXSL - we.SL},KCJE=${(CXSL - we.SL) *
        we.CBJJ} WHERE BMBM = '07' AND YPBM = '${we.YPBM}' AND SCPH = '${we.SCPH}' AND CKBM = '${we.CKBM}' `;
    // judgeList.forEach(item => {
    //     if (we[item]) {
    //         updateKcSql += ` AND ${item}= ${item === 'CBJJ' ? '' : '\''}${we[item]}${item === 'CBJJ' ? '' : '\''}`;
    //     }
    // });
    return updateKcSql
    // return sql.query(updateKcSql);

}

/**
 * 写入销售订单MX表
 */
const updateMX = (we, newID, shijian, nb, day, abs) => {
    return `INSERT INTO XSD_MX (ID,XH,NB,YPBM,YPMC,YPGG,JXMC,SCCJ,CDMC,PZWH,SCPH,SCRQ,YXQX,JXQX,YXQZ,JLDW,MJSL,XS,SL,JS,SS,YJ,YJ_HS,ZK,DJ,DJ_HS,
        DJ_LSJ,DJ_ZDJ,JE,RT,SE,HJ,CBJJ,XSCB,XSML,CKBM,CKMC,ZP,DZ,JF,CB,FHRQ,BZQK,ZLZK,FHJL,HWBH) SELECT '${newID}', ${shijian},${parseInt(
        nb
    )},'${we.YPBM}','${we.YPMC}','${we.YPGG}','${we.JXMC}','${we.SCCJ}','${
        we.CDMC
        }','${we.PZWH}','${we.SCPH}','${we.SCRQ}',${day},${30},'${we.YXQZ}','${
        we.JLDW
        }',${0},${we.SL},${we.SL},${0},${we.SL},${we.DJ},${we.DJ},${we.ZK},${
        we.DJ
        },${we.DJ},${we.DJ},${0},${we.JE},${0},${0},${we.HJ},${we.CBJJ},${
        we.CBJJ * we.SL
        },${we.XSML},'${we.CKBM}','${
        we.CKMC
        }','F','T','T','A','${abs}','合格','合格','合格','${we.HWBH}'`
    // return sql.query`INSERT INTO XSD_MX (ID,XH,NB,YPBM,YPMC,YPGG,JXMC,SCCJ,CDMC,PZWH,SCPH,SCRQ,YXQX,JXQX,YXQZ,JLDW,MJSL,XS,SL,JS,SS,YJ,YJ_HS,ZK,DJ,DJ_HS,DJ_LSJ,DJ_ZDJ,JE,RT,SE,HJ,CBJJ,XSCB,XSML,CKBM,CKMC,ZP,DZ,JF,CB,FHRQ,BZQK,ZLZK,FHJL,HWBH) SELECT ${newID}, ${shijian},${parseInt(
    //     nb
    // )},${we.YPBM},${we.YPMC},${we.YPGG},${we.JXMC},${we.SCCJ},${
    //     we.CDMC
    //     },${we.PZWH},${we.SCPH},${we.SCRQ},${day},${30},${we.YXQZ},${
    //     we.JLDW
    //     },${0},${we.SL},${we.SL},${0},${we.SL},${we.DJ},${we.DJ},${we.ZK},${
    //     we.DJ
    //     },${we.DJ},${we.DJ},${0},${we.JE},${0},${0},${we.HJ},${we.CBJJ},${
    //     we.CBJJ * we.SL
    //     },${we.XSML},${we.CKBM},${
    //     we.CKMC
    //     },${'F'},${'T'},${'T'},${'A'},${abs},${'合格'},${'合格'},${'合格'},${we.HWBH}`
}

module.exports = {
    reviewsCount,
    reviewsOrder,
    sendOrderData,
    sendDetail,
    sendStream,
    setRemark,
    isWrite,
    updateZB,
    updateJS,
    updateKC,
    updateMX,
    getZB
}
